#!/usr/bin/perl

package DBUtil;

require Exporter;

our @ISA = qw(Exporter);

our @EXPORT = qw(
    trim
    MakeLocalStr
    MakeUtf8Str
    ValidDate
    ValidNumber
    BackupDB
    GetRowsets
    GetRowsetArray
    FillList
    ExportToFile
    ImportFromFile
    DeleteRecord
    AddRecord
    UpdateRecord
);

use strict;
use DBI;
use Encode;
use Win32::GUI;
use List::Util qw/min max/;
use File::Copy;
use Data::Dumper;

use Const;

# exports
sub trim($)
{
    my $str = shift;
    $str =~ s/^\s+//;
    $str =~ s/\s+$//;

    return $str;
}

sub MakeLocalStr($)
{
    my $str_utf8 = shift;

    my $str_local = encode("euc-cn", decode_utf8($str_utf8));

    return $str_local;
}

sub MakeUtf8Str($)
{
    my $str_local = shift;

    my $str_utf8 = encode_utf8(decode("euc-cn", $str_local));

    return $str_utf8;
}

sub ValidDate
{
    my ($win, $value, $caption) = @_;

    if ($value !~ /\d{4}-\d{2}-\d{2}/) {
        $win->MessageBox("$caption format", "Error",MB_OK|MB_ICONHAND);
        return BAD;
    }

    return GOOD;
}

sub ValidNumber
{
    my ($win, $value, $caption) = @_;

    return GOOD if ($value =~ /^-?[\d\.]+$/);

    $win->MessageBox( "$caption format", "Error", MB_OK|MB_ICONHAND);
    return BAD;
}

sub BackupDB
{
    my $db = shift;
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);

    my $time_str = sprintf "%04d%02d%02d", $year+1900, $mon+1, $mday;

    return if (-f "$DBPATH/${db}_${time_str}.db3");

    copy("$DBPATH/$db.db3", "$DBPATH/${db}_${time_str}.db3");

    return GOOD
}

sub GetRowsets
{
    my ($db, $table, $fields, $where, $order) = @_;

    my $dbh = openDB($db);

    my $sqlstr = "SELECT $fields FROM $table";
    $sqlstr .= " WHERE $where" if ($where);
    $sqlstr .= " ORDER BY $order" if ($order);

#print "$sqlstr\n";

    my $sth = $dbh->prepare($sqlstr) || die "Error in $sqlstr\n";
    $sth->execute();

    my @rowsets;
    while (my $row = $sth->fetchrow_hashref()) {
        push @rowsets, $row;
    }

    $sth->finish();
    $dbh->disconnect();

    return @rowsets;
}

sub FillList
{
    my ($db, $table, $lv, $order) = @_;

    $lv->Clear();
    while (1) {
        my $col = $lv->GetColumn(0);
        last unless(defined($col));

        $lv->DeleteColumn(0);
    }

    my @rows = GetRowsets("index", "field_t", "*", "t_name = '$db.$table'", "f_id");

    my @fields;
    for my $row (@rows) {
        $lv->InsertColumn(
            -text => MakeLocalStr($row->{f_title}),
            -width => ($row->{f_type} =~ /(integer|float)/) ? FONTSIZE*8
                                            : FONTSIZE*(min(20,$row->{f_size})),
        );
        push @fields, $row->{f_name};
    }

    my @datarows = GetRowsets($db, $table, join(",", @fields), "1", $order);

    for my $row (@datarows) {
        my @items;
        for my $field (@fields) {
            push @items, MakeLocalStr($row->{$field});
        }
        $lv->InsertItem(-text => \@items);
    }

    $lv->Select(-1);

}

sub ExportToFile
{
    my ($db, $table, $file) = @_;

    open my $FH, '>', $file or return BAD;

    my @rows = GetRowsets("index", "field_t", "*", "t_name = '$db.$table'", "f_id");

    my @fields;
    print FH "# ";

    for my $row (@rows) {
        print FH $row->{f_title} . ",";
        push @fields, $row->{f_name};
    }
    print $FH "\n";

    my @datarows = GetRowsets($db, $table, join(",", @fields));
    for my $row (@datarows) {
        for my $field (@fields) {
            print $FH $row->{$field} . ",";
        }
        print $FH "\n";
    }

    close $FH;
    return GOOD;
}

sub ImportFromFile
{
    my ($db, $table, $file) = @_;

    BackupDB($db);
    my @rows = GetRowsets("index", "field_t", "*", "t_name = '$db.$table'", "f_id");

    my @fieldinfo;
    for my $row (@rows) {
        push @fieldinfo, {name => $row->{f_name}, type => $row->{f_type}};
    }

    my $dbh = openDB($db, 1);

    open my $FH, '<', $file or return BAD;

    my $errs = 0;
    while (<$FH>) {
        next if (/^\s*#/);
        next if (/^\s*$/);

        chomp;

        my @values = split /,/;
        my $sqlstr = "INSERT INTO $table VALUES(";
        for my $index ( 0 .. $#fieldinfo) {
            $sqlstr .= ", " if ($index != 0);

            my $type = $fieldinfo[$index]->{type};
            if (grep(/^$type$/, ("varchar", "char"))) {
                $sqlstr .= "'" . trim($values[$index]) . "'";
            }
            else {
                $sqlstr .= trim($values[$index]);
            }
        }
        $sqlstr .= ")";

        $dbh->do($sqlstr);

        if ($dbh->err()) {
            $sqlstr .= "  FAILED";
            $errs++;
        }
        else { $sqlstr .= "  PASSED"; }

        print "$sqlstr\n";
    }

    close $FH;

    $dbh->commit();
    $dbh->disconnect();

    return $errs;
}

sub DeleteRecord
{
    my ($db, $table, $where) = @_;

    my $dbh = openDB($db);

    my $sqlstr = "DELETE FROM $table WHERE $where";
print "$sqlstr\n";

    $dbh->do($sqlstr);

    my $rc = $dbh->err() ? BAD : GOOD;

    $dbh->disconnect();

    return $rc;
}

sub AddRecord
{
    my ($db, $table, $values) = @_;

    my $dbh = openDB($db);

    my $sqlstr = "INSERT INTO $table VALUES ($values)";
print "$sqlstr\n";

    $dbh->do($sqlstr);

    my $rc = $dbh->err() ? BAD : GOOD;

    $dbh->disconnect();

    return $rc;
}

sub UpdateRecord
{
    my ($db, $table, $updatestr, $where) = @_;

    my $dbh = openDB($db);

    my $sqlstr = "UPDATE $table SET $updatestr WHERE $where";
print "$sqlstr\n";

    $dbh->do($sqlstr);

    my $rc = $dbh->err() ? BAD : GOOD;

    $dbh->disconnect();

    return $rc;
}

#internal subs
sub openDB
{
    my ($db, $transaction) = @_;

    my $autocommit = $transaction ? 0 : 1;

    my $dbh = DBI->connect("dbi:SQLite:dbname=$DBPATH/$db.db3", "", "",
                    {AutoCommit => $autocommit}  ) or die $DBI::errstr;

    $dbh->do('PRAGMA foreign_keys = ON');
    return $dbh;
}


1;
